package sisfo.astacala.util;

import com.mysql.jdbc.Connection;
import com.mysql.jdbc.PreparedStatement;
import com.mysql.jdbc.ResultSetMetaData;
import com.mysql.jdbc.Statement;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author arif
 */
public class UtilDatabase {

    private static UtilDatabase utilDatabase;
    private Connection connection;

    public static UtilDatabase getInstance() {
        if (utilDatabase == null) {
            utilDatabase = new UtilDatabase();
        }
        return utilDatabase;
    }

    public void openConnection() {
        try {
            String url = "jdbc:mysql://localhost:3306/astacala";
            String username = "root";
            String password = "";
            Class.forName("com.mysql.jdbc.Driver");
            connection = (Connection) DriverManager.getConnection(url, username, password);
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(UtilDatabase.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            Logger.getLogger(UtilDatabase.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public List executeQuery(String query, List parameters) {
        List result = new ArrayList();
        try {
            Statement statement = (Statement) connection.createStatement();
            PreparedStatement pStatement = (PreparedStatement) connection.prepareStatement(query);
            for (int i = 0; i < parameters.size(); i++) {
                pStatement.setObject(i, parameters.get(i));
            }
            ResultSet resultSet = pStatement.executeQuery();
            ResultSetMetaData resultSetMetadata = (ResultSetMetaData) pStatement.getMetaData();

            while (resultSet.next()) {
                Map tempRowData = new HashMap();
                for (int i = 1; i <= resultSetMetadata.getColumnCount(); i++) {
                    String columnName = resultSetMetadata.getColumnName(i);
                    tempRowData.put(columnName, resultSet.getString(columnName));
                }
//                System.out.println(tempRowData.keySet());
                result.add(tempRowData);
            }
        } catch (SQLException ex) {
            Logger.getLogger(UtilDatabase.class.getName()).log(Level.SEVERE, null, ex);
        }
        return result;
    }

    public int executeUpdate(String query, List parameters) {
        int result = 0;
        try {
            PreparedStatement pStatement = (PreparedStatement) connection.prepareStatement(query);            
            for (int i = 0; i < parameters.size(); i++) {
                pStatement.setObject(i + 1, parameters.get(i));
            }
            System.out.println(pStatement.toString());
            result = pStatement.executeUpdate();

        } catch (SQLException ex) {
            Logger.getLogger(UtilDatabase.class.getName()).log(Level.SEVERE, null, ex);
        }
        return result;
    }

    public void closeConnection() {
        try {
            connection.close();
        } catch (SQLException ex) {
            Logger.getLogger(UtilDatabase.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public static void main(String args[]) {
        UtilDatabase.getInstance().openConnection();
        UtilDatabase.getInstance().executeQuery("select * from tbls_column", new ArrayList());
        UtilDatabase.getInstance().closeConnection();
    }
}